package sklad;

import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * User: Vladimir
 * Date: 23.09.12 Time: 19:36
 */
public class Parser {

    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yy");

    public static void main(String[] args) throws BiffException, IOException, ParseException, WriteException {
//        String path = "G:\\projects\\my-im-tools\\data\\remains\\extramobi.xls";
//        String inPath = "G:\\projects\\my-im-tools\\sklad\\in-bills\\";
//        String sellsPath = "G:\\projects\\my-im-tools\\sklad\\sells\\";
//        String path = sellsPath + "Алтайская,16 09.10.12.xls";
//        String inFile = "ISA 19.09.12.xls";
//        List<GoodItem> items = parseRemainsGorohovaya(path);
//        List<GoodItem> items = parseRemainsAltayskaya(path);
//        List<GoodItem> items = parseXls(inPath + inFile);
//        Map<SourceEnum, List<GoodItem>> itemsBySource = parseDir(path);
//        Collections.sort(items, new Comparator<GoodItem>() {
//            @Override
//            public int compare(GoodItem o1, GoodItem o2) {
//                return o1.article.compareTo(o2.article);
//            }
//        });
//        for (GoodItem item : items) {
//            System.out.println(item);
//        }
//        archieveDir("G:\\projects\\my-im-tools\\test\\in-bills\\");
//        archieveDir("G:\\projects\\my-im-tools\\test\\out-bills\\");
//        archieveAll("G:\\projects\\my-im-tools\\test\\");
//        List<GoodItem> items = parseXlsPOI("sklad2/income/Полярников,6 07.01.2013.xlsx");
//        List<GoodItem> items = parseXlsPOI("sklad2/income/Гороховая,49 07.01.2013.xlsx");
        List<GoodItem> items = parseRemainsGorohovayaPOI("sklad2/remains/Гороховая,49 09.01.13.xlsx");
        for (GoodItem item : items) {
            System.out.println(item);
        }
    }

    private static void archieveAll(String path) throws BiffException, IOException, WriteException {
        File rootFolder = new File(path);
        for (File folder : rootFolder.listFiles()) {
            if (folder.isFile()) continue;
            if (!folder.getName().contains("bills")) continue;
            archieveDir(folder.getPath() + "\\");
        }
    }

    public static void archieveDir(String path) throws BiffException, IOException, WriteException {
        File folder = new File(path);
        Map<String, List<File>> filesBySource = new HashMap<String, List<File>>();
        for (File file : folder.listFiles()) {
            if (file.isDirectory()) continue;
            String source = file.getName().split("\\s")[0];
            if (filesBySource.containsKey(source)) {
                filesBySource.get(source).add(file);
            } else {
                List<File> files = new ArrayList<File>();
                files.add(file);
                filesBySource.put(source, files);
            }
            System.out.println(file);
        }
        for (String source : filesBySource.keySet()) {
            List<File> files = filesBySource.get(source);
            if (files.size() > 1) {
                List<GoodItem> goodItemsBySource = new ArrayList<GoodItem>();
                for (File file : files) {
                    List<GoodItem> goodItems = parseXls(file.getPath());
                    moveItems(goodItems, goodItemsBySource);
                }
                saveItemsToXls(path, source, goodItemsBySource);
                moveToArchieve(path, files);
                System.out.println(source + ": " + goodItemsBySource.size());
            }
        }
    }

    private static void moveToArchieve(String path, List<File> files) {
        for (File file : files) {
            boolean moved = file.renameTo(new File(path + "archieve/" + file.getName()));
            if (!moved) {
                throw new IllegalStateException(file + " not moved to " + path + "archieve\\");
            }
        }
    }

    private static void saveItemsToXls(String path, String source, List<GoodItem> goodItemsBySource) throws IOException, WriteException {
        String xlsFile = path + source + " " + dateFormat.format(new Date()) + ".xls";
        WorkbookSettings s = new WorkbookSettings();
        s.setLocale(new Locale("ru", "RU"));
        s.setEncoding("KOI8-R");
        WritableWorkbook workbook = Workbook.createWorkbook(new File(xlsFile), s);
        workbook.createSheet("Остатки", 0);
        WritableSheet sheet = workbook.getSheet(0);
        System.out.println(xlsFile);

        sortItems(goodItemsBySource);


        int count = 0;
        int row = 0;
        WritableCell cell = new Label(count++, row, "№");
        sheet.addCell(cell);
        cell = new Label(count++, row, "артикул");
        autoFitWidth(sheet, count);
        sheet.addCell(cell);
        cell = new Label(count++, row, "наименование");
        autoFitWidth(sheet, count);
        sheet.addCell(cell);
        cell = new Label(count++, row, "кол-во");
        autoFitWidth(sheet, count);
        sheet.addCell(cell);
        cell = new Label(count++, row, "цена");
        autoFitWidth(sheet, count);
        sheet.addCell(cell);
        cell = new Label(count, row, "сумма");
        autoFitWidth(sheet, count);
        sheet.addCell(cell);

        for (GoodItem item : goodItemsBySource) {
            count = 0;
            row++;
            double price = item.buyPrice == 0 ? item.sellPrice : item.buyPrice;
            double summ = price * item.count;
            cell = new Number(count++, row, row);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
            cell = new Label(count++, row, item.article);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
            cell = new Label(count++, row, item.name);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
            cell = new Number(count++, row, item.count);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
            cell = new Number(count++, row, price);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
            cell = new Number(count, row, summ);
            autoFitWidth(sheet, count);
            sheet.addCell(cell);
        }
        workbook.write();
        workbook.close();
    }

    public static void autoFitWidth(WritableSheet sheet, int column) {
        CellView view = sheet.getColumnView(column);
        view.setAutosize(true);
        sheet.setColumnView(column, view);
    }

    private static void sortItems(List<GoodItem> items) {
        Collections.sort(items, new Comparator<GoodItem>() {
            @Override
            public int compare(GoodItem o1, GoodItem o2) {
                return o1.article.compareTo(o2.article);
            }
        });
    }


    public static Map<StoreEnum, List<GoodItem>> parseRemainsDir(String path) throws BiffException, IOException {
        Map<StoreEnum, List<GoodItem>> itemsByStore = new HashMap<StoreEnum, List<GoodItem>>();
        File storeFolder = new File(path);
        for (File storeFile : storeFolder.listFiles()) {
            if (storeFile.getName().contains("post.xls") || storeFile.getName().contains("self.xls")) {
                continue;
            }
            StoreEnum store = parseStore(storeFile);
            List<GoodItem> items;
            if (store.equals(StoreEnum.ALTAYSKAYA)) {
                items = parseRemainsAltayskaya(storeFile.getPath());
            } else if (store.equals(StoreEnum.GOROHOVAYA)) {
                items = parseRemainsGorohovaya(storeFile.getPath());
            } else {
                continue;
            }
            itemsByStore.put(store, items);
        }
        return itemsByStore;
    }

    private static List<GoodItem> parseRemainsAltayskaya(String path) throws BiffException, IOException {
        Workbook workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(0);
        List<GoodItem> items = new ArrayList<GoodItem>();
        for (int row = 1; row < sheet.getRows(); row++) {
            GoodItem item = new GoodItem();
            String contents = sheet.getCell(0, row).getContents();
            if (contents.isEmpty()) {
                break;
            }
            String[] fields = contents.split("\\s");
            contents = sheet.getCell(2, row).getContents();
            String article = fields[fields.length - 1];
            String name = "";
            for (int i = 0; i < fields.length - 1; i++) {
                name = name + " " + fields[i] + " ";
            }
            name = name.trim();
            int count = Integer.parseInt(contents);
            item.name = name;
            item.article = article;
//            item.supplier = "yaric";
            item.count = count;
            items.add(item);
        }
        return items;
    }

    public static StoreEnum parseStore(File storeFile) {
        String name = parseSourceName(storeFile.getName());
        return getStoreByName(name);
    }

    public static List<GoodItem> parseRemainsGorohovayaPOI(String path) throws BiffException, IOException {
        List<GoodItem> items = new ArrayList<GoodItem>();
        if (path.endsWith("xlsx")) {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));
            XSSFSheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                GoodItem item = new GoodItem();
                String articleName = row.getCell(0).getStringCellValue();
                String[] fields = articleName.split(",")[0].split("\\s");
                String article = fields[0];
                String name = "";
                for (int i = 1; i < fields.length; i++) {
                    name = name + " " + fields[i] + " ";
                }
                name = name.trim();
                item.article = article;
                item.name = name;
                int count = (int) row.getCell(4).getNumericCellValue();
                item.count = count;
                items.add(item);
            }
        } else {
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(path));
            HSSFSheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                GoodItem item = new GoodItem();
                String articleName = row.getCell(0).getStringCellValue();
                String[] fields = articleName.split(",")[0].split("\\s");
                String article = fields[0];
                String name = "";
                for (int i = 1; i < fields.length; i++) {
                    name = name + " " + fields[i] + " ";
                }
                name = name.trim();
                item.article = article;
                item.name = name;
                int count = (int) row.getCell(4).getNumericCellValue();
                item.count = count;
                items.add(item);
            }
        }
        return items;
    }

    public static List<GoodItem> parseRemainsGorohovaya(String path) throws BiffException, IOException {
        Workbook workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(0);
        List<GoodItem> items = new ArrayList<GoodItem>();
        for (int row = 1; row < sheet.getRows(); row++) {
            GoodItem item = new GoodItem();
            String contents = sheet.getCell(0, row).getContents();
            if (contents.isEmpty()) {
                break;
            }
            String[] fields = contents.split(",")[0].split("\\s");
            String article = fields[0];
            String name = "";
            for (int i = 1; i < fields.length; i++) {
                name = name + " " + fields[i] + " ";
            }
            name = name.trim();
            contents = sheet.getCell(3, row).getContents();
            int count = Integer.parseInt(contents);
            item.name = name;
            item.article = article;
//            item.supplier = "yaric";
            item.count = count;
            items.add(item);
        }
        return items;
    }

    public static Map<StoreEnum, List<GoodItem>> parseDirByStore(String path) throws BiffException, IOException, ParseException {
        Map<StoreEnum, List<GoodItem>> itemsByStore = new HashMap<StoreEnum, List<GoodItem>>();
        File storeFolder = new File(path);
        for (File storeFile : storeFolder.listFiles()) {
            if (storeFile.getName().equals("archieve")) continue;
            String storeName = storeFile.getName();
            List<GoodItem> itemsByFile = parseXls(path + storeName);
            StoreEnum store = parseStore(storeFile);
            if (itemsByStore.containsKey(store)) {
                List<GoodItem> storeItems = itemsByStore.get(store);
                moveItems(itemsByFile, storeItems);
            } else {
                itemsByStore.put(store, itemsByFile);
            }
        }
        return itemsByStore;
    }


    public static List<GoodItem> parseDir(String path) throws BiffException, IOException, ParseException {
//        Map<SourceEnum, List<GoodItem>> itemsBySource = new HashMap<SourceEnum, List<GoodItem>>();
        File sourceFolder = new File(path);
        List<GoodItem> items = new ArrayList<GoodItem>();
        for (File sourceFile : sourceFolder.listFiles()) {
            if (sourceFile.isDirectory()) continue;
            String sourceName = sourceFile.getName();
            List<GoodItem> itemsByFile = parseXls(path + sourceName);
//            if (itemsBySource.containsKey(source)) {
//                items = itemsBySource.get(source);
//            }
            moveItems(itemsByFile, items);
//            for (GoodItem item : items) {
//
//            }
//            items.addAll(shiftItems);
        }
        return items;
    }

    public static void moveItems(List<GoodItem> fromItems, List<GoodItem> toItems) {
        for (GoodItem fromItem : fromItems) {
            GoodItem toItem = new GoodItem();
            if (toItems.contains(fromItem)) {
                toItem = toItems.get(toItems.indexOf(fromItem));
            } else {
                toItem.buyPrice = fromItem.buyPrice;
                toItem.sellPrice = fromItem.sellPrice;
                toItem.supplier = fromItem.supplier;
                toItem.article = fromItem.article;
                toItem.name = fromItem.name;
            }
            toItem.count += fromItem.count;
            fromItem.count = 0;
            if (!toItems.contains(toItem)) {
                toItems.add(toItem);
            }
        }

    }

    private static SourceEnum getSourceByName(String name) {
        SourceEnum[] values = SourceEnum.values();
        for (SourceEnum source : values) {
            if (source.name.equals(name)) {
                return source;
            }
        }
//        throw new IllegalArgumentException("Unknown source name: " + name);
        return SourceEnum.UNKNOWN;
    }


    private static StoreEnum getStoreByName(String name) {
        StoreEnum[] values = StoreEnum.values();
        for (StoreEnum source : values) {
            if (source.name.equals(name)) {
                return source;
            }
        }
        throw new IllegalArgumentException("Unknown store name: " + name);
//        return StoreEnum.UNKNOWN;
    }

//    public static List<GoodItem> parseSellsXls(String path) throws BiffException, IOException {
//        Workbook workbook = Workbook.getWorkbook(new File(path));
//        Sheet sheet = workbook.getSheet(0);
//        List<GoodItem> items = new ArrayList<GoodItem>();
//        for (int row = 0; row < sheet.getRows(); row++) {
//            try {
//                Integer.parseInt(sheet.getCell(0, row).getContents());
//                GoodItem item = new GoodItem();
//                item.count = Integer.parseInt(sheet.getCell(3, row).getContents());
//                item.article = sheet.getCell(1, row).getContents();
//                item.name = sheet.getCell(2, row).getContents();
//
//            } catch (NumberFormatException e) {
//                continue;
//            }
//        }
//        return items;
//    }

    public static List<GoodItem> parseXlsPOI(String path) throws BiffException, IOException {
        List<GoodItem> items = new ArrayList<GoodItem>();
        if (path.endsWith("xlsx")) {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));
            XSSFSheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getCell(0) == null || row.getCell(0).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    continue;
                }
                String article = getStringValue(row.getCell(1));
                String name = row.getCell(2).getStringCellValue();
                int count = (int) row.getCell(3).getNumericCellValue();
                GoodItem item = new GoodItem();
                item.article = article;
                item.count = count;
                item.name = name;
                items.add(item);
            }
        } else {
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(path));
            HSSFSheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getCell(0) == null || row.getCell(0).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                    continue;
                }
                String article = getStringValue(row.getCell(1));
                String name = row.getCell(2).getStringCellValue();
                int count = (int) row.getCell(3).getNumericCellValue();
                GoodItem item = new GoodItem();
                item.article = article;
                item.count = count;
                item.name = name;
                items.add(item);
            }
        }
        return items;
    }

    public static String getStringValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return (int) cell.getNumericCellValue() + "";
        } else {
            return cell.getStringCellValue();
        }
    }

    public static List<GoodItem> parseXls(String path) throws BiffException, IOException {
        Workbook workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(0);
        List<GoodItem> items = new ArrayList<GoodItem>();
        for (int row = 0; row < sheet.getRows(); row++) {
            try {
                Integer.parseInt(sheet.getCell(0, row).getContents());
            } catch (NumberFormatException e) {
                continue;
            }
            String name = parseSourceName(path);
            SourceEnum source = getSourceByName(name);
//                Date date = dateFormat.parse(fields[1]);
            GoodItem item = new GoodItem();
            item.count = Integer.parseInt(sheet.getCell(3, row).getContents());
            item.buyPrice = Double.parseDouble(sheet.getCell(4, row).getContents());
            item.article = sheet.getCell(1, row).getContents();
            item.name = sheet.getCell(2, row).getContents();
            item.supplier = source;
            items.add(item);
        }
        return items;
    }

    private static String parseSourceName(String path) {
        String sourceName = new File(path).getName().replace(".xls", "");
        String[] fields = sourceName.split("\\s");
        return fields[0];
    }
}